﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading;
using System.Xml;
using Common.Utilities;
using Common.Utilities.Dispatcher;
using Common.Utilities.OpenXml;

namespace Workflows.Components.Entities.MasterData
{
    /// <summary>
    /// 
    /// </summary>
    public class DbProfileWorker:IWorker 
    {
        #region Implementation of IWorker

        public event WorkerStarted WorkerStartedEvent;
        public event WorkerFinished WorkerFinishedEvent;
        public event WorkerHasError WorkerErredEvent;
        public event WorkerCancelled WorkerCancelledEvent;
        public event WorkerStatusChanged WorkerStatusChangedEvent;

        private Thread workerThread;
        private DbProfileInstruction instruction;
        private DbProfileOutput output;
        private RunContext _Context;
        private Guid _TicketID;
        private string _WorkflowName;
        private string _ComponentName;

        /// <summary>
        /// 
        /// </summary>
        public string ComponentName
        {
            get { return _ComponentName; }
        }

        /// <summary>
        /// 
        /// </summary>
        public string WorkflowName
        {
            get { return _WorkflowName; }
        }

        /// <summary></summary>
        public Thread WorkerThread
        {
            get { return workerThread; }
        }
        /// <summary></summary>
        public InstructionBase Instruction
        {
            get { return instruction; }
        }
        /// <summary></summary>
        public ResultBase Output
        {
            get { return output; }
        }
        /// <summary></summary>
        public Type ComponentType
        {
            get { return typeof(DbProfileComponent); }
        }
        /// <summary></summary>
        public RunContext Context
        {
            get { return _Context; }
            set { _Context = value; }
        }
        /// <summary></summary>
        public void SetInstruction(Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._TicketID = ticketGuid;
            this.instruction = (DbProfileInstruction)request;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }
        /// <summary></summary>
        public void SetInstruction(RunContext context, Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._TicketID = ticketGuid;
            this.instruction = (DbProfileInstruction)request;
            this._Context = context;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }
        /// <summary></summary>
        public void DoWork()
        {
            if (this.WorkerStartedEvent != null)
            {
                this.WorkerStartedEvent(this._TicketID, this, this.GetType(), this.instruction);
            }
            this.workerThread = Thread.CurrentThread;
            this.ProfileDatabase();
        }
        /// <summary></summary>
        public void DoWorkAsync()
        {
            if (this.WorkerStartedEvent != null)
            {
                this.WorkerStartedEvent(this._TicketID, this, this.GetType(), this.instruction);
            }
            this.workerThread = new Thread(new ThreadStart(this.ProfileDatabase));
            this.workerThread.Start();
        }
        /// <summary></summary>
        public void CancelWork()
        {
            if (this.workerThread != null && this.workerThread.IsAlive)
            {
                this.workerThread.Abort();
                this.workerThread.Join();

                if (this.WorkerCancelledEvent != null)
                {
                    this.WorkerCancelledEvent(this._TicketID, this.GetType(), this.instruction);
                }
            }
        }

        #endregion

        #region work
        private void ProfileDatabase()
        {
            string connStr = string.Format(
                "Server={0};Database={1};Integrated Security=SSPI;",
                this.instruction.DataSourceName, this.instruction.DbName);
            if (!this.instruction.IntegratedSecurity)
                connStr = string.Format(
                    "Server={0};Database={1};User ID={2};Password={3}",
                    this.instruction.DataSourceName, this.instruction.DbName,
                    this.instruction.DbUserName, this.instruction.DbPassword);
            Dictionary<string, Dictionary<string, Dictionary<string, FieldStat>>> schemaTblColFieldStats =
                new Dictionary<string, Dictionary<string, Dictionary<string, FieldStat>>>();
            try
            {
                int tblCount = 0;
                foreach(string schemaName in instruction.TablesBySchemas.Keys)
                {
                    tblCount += instruction.TablesBySchemas[schemaName].Count;
                }

                int tblProfiled = 0;
                foreach(string schemaName in this.instruction.TablesBySchemas.Keys)
                {
                    Dictionary<string, Dictionary<string, FieldStat>> tblColFieldStats =
                        new Dictionary<string, Dictionary<string, FieldStat>>();
                    foreach(string tblName in this.instruction.TablesBySchemas[schemaName])
                    {
                        if(this.WorkerStatusChangedEvent !=null)
                        {
                            int pct = tblProfiled*100/tblCount;
                            string msg = string.Format(
                                "Profiling table {0}, profiled {1} of {2} tables", tblName, tblProfiled, tblCount);
                            this.WorkerStatusChangedEvent(
                                this._TicketID, this.GetType(), this.instruction, this.WorkflowName, this.ComponentName,
                                msg, pct);
                        }

                        TableColumn[] cols = DbUtil.GetTableColumns(
                            DataProviderType.MSSQL, connStr, schemaName, tblName);
                        if(cols==null)
                            continue;
                        Dictionary<string, SqlDbType> colDbs = new Dictionary<string, SqlDbType>();
                        foreach(TableColumn col in cols)
                            colDbs.Add(col.ColumnName, col.DbType);
                        Dictionary<string, FieldStat> fieldStats = new Dictionary<string, FieldStat>();
                        if (this.instruction.ProfileIndexedColumnsOnly)
                        {
                            string[] indexedColumnNames = DbUtil.GetTableIndexedColumnNames(connStr, tblName);
                            if (indexedColumnNames == null || indexedColumnNames.Length == 0)
                                continue;
                            foreach (string colName in indexedColumnNames)
                            {
                                FieldStat fieldStat =
                                    DbUtil.GetTableFieldStat(
                                        DataProviderType.MSSQL, connStr, schemaName, tblName, colName, colDbs[colName]);
                                fieldStats.Add(colName, fieldStat);
                            }
                        }
                        else
                        {
                            foreach(string colName in colDbs.Keys)
                            {
                                try
                                {
                                    FieldStat fieldStat =
                                        DbUtil.GetTableFieldStat(
                                            DataProviderType.MSSQL, connStr, schemaName, tblName, colName,
                                            colDbs[colName]);
                                    fieldStats.Add(colName, fieldStat);
                                }
                                catch(Exception ex1)
                                {
                                    if (this.WorkerErredEvent != null)
                                        this.WorkerErredEvent(this._TicketID, this.GetType(), this.instruction, ex1.ToString(), false);
                                }
                            }
                        }
                        tblColFieldStats.Add(tblName,fieldStats);

                        tblProfiled++;
                    }
                    schemaTblColFieldStats.Add(schemaName,tblColFieldStats);
                }

                if(this.instruction.OutputFilePath.ToLower().EndsWith(".xlsx"))
                {
                    this.SaveResultToExcel(this.instruction.OutputFilePath,
                        schemaTblColFieldStats);
                }
                else
                {
                    this.SaveResultToXml(this.instruction.OutputFilePath,
                        schemaTblColFieldStats);
                }

                this.output=new DbProfileOutput(this.instruction.OwnerComponentID,this._TicketID,
                    this.instruction.OutputFilePath);
                if (this.WorkerFinishedEvent != null)
                    this.WorkerFinishedEvent(this._TicketID, this.GetType(), this.instruction, this.output);
            }
            catch(Exception ex)
            {
                if (this.WorkerErredEvent != null)
                    this.WorkerErredEvent(this._TicketID, this.GetType(), this.instruction, ex.ToString(), true);
            }
        }
        #endregion

        #region xml
        private void SaveResultToXml(string xmlFilePath,
            Dictionary<string, Dictionary<string, Dictionary<string, FieldStat>>> schemaTblColFieldStats)
        {
            XmlDocument xDoc = new XmlDocument();
            xDoc.LoadXml("<DbProfile></DbProfile>");
            XmlNode root = xDoc.DocumentElement;
            foreach (string schemaName in schemaTblColFieldStats.Keys)
            {
                XmlNode schemaNode = XmlDataUtil.AddElement(ref xDoc, root, "Schema");
                XmlDataUtil.UpdateAttribute(ref xDoc, schemaNode, "SchemaName", schemaName);
                foreach (string tblName in schemaTblColFieldStats[schemaName].Keys)
                {
                    XmlNode tblNode = XmlDataUtil.AddElement(ref xDoc, schemaNode, "Table");
                    XmlDataUtil.UpdateAttribute(ref xDoc, tblNode, "TableName", tblName);
                    foreach (string colName in schemaTblColFieldStats[schemaName][tblName].Keys)
                    {
                        FieldStat fieldStat = schemaTblColFieldStats[schemaName][tblName][colName];
                        XmlNode statNode = XmlDataUtil.AddElement(ref xDoc, tblNode, "FieldStat");
                        XmlDataUtil.UpdateAttribute(ref xDoc, statNode, "FieldName", fieldStat.FieldName);
                        XmlDataUtil.UpdateAttribute(ref xDoc, statNode, "DataType", fieldStat.DataType.ToString());
                        XmlDataUtil.UpdateAttribute(ref xDoc, statNode, "Count", fieldStat.Count.ToString());
                        XmlDataUtil.UpdateAttribute(
                            ref xDoc, statNode, "DistinctCount", fieldStat.DistinctValueCount.ToString());
                        XmlDataUtil.UpdateAttribute(
                            ref xDoc, statNode, "NullCount", fieldStat.MissingValueCount.ToString());
                        XmlDataUtil.UpdateAttribute(
                            ref xDoc, statNode, "Max",
                            fieldStat.MaxValue == null ? "" : fieldStat.MaxValue.ToString());
                        XmlDataUtil.UpdateAttribute(
                            ref xDoc, statNode, "Min",
                            fieldStat.MinValue == null ? "" : fieldStat.MinValue.ToString());
                        XmlDataUtil.UpdateAttribute(
                            ref xDoc, statNode, "Mean",
                            fieldStat.Average == null ? "" : fieldStat.Average.ToString());
                        XmlDataUtil.UpdateAttribute(
                            ref xDoc, statNode, "Mode", fieldStat.Mode == null ? "" : fieldStat.Mode.ToString());
                        XmlDataUtil.UpdateAttribute(
                            ref xDoc, statNode, "SD",
                            fieldStat.SD == null ? "" : fieldStat.SD.ToString());
                        string distValueStr = string.Empty;
                        if (fieldStat.Distributions != null && fieldStat.Distributions.Count > 0)
                        {
                            foreach (object val in fieldStat.Distributions.Keys)
                            {
                                XmlNode nomNode = XmlDataUtil.AddElement(ref xDoc, statNode, "NominalValue");
                                XmlDataUtil.UpdateAttribute(ref xDoc, nomNode, "Value", val.ToString());
                                XmlDataUtil.UpdateAttribute(ref xDoc, nomNode, "Frequency", fieldStat.Distributions[val].ToString());
                            }
                        }
                        XmlDataUtil.UpdateAttribute(ref xDoc, statNode, "NominalValues", distValueStr);
                    }
                }
            }
            xDoc.Save(xmlFilePath);
        }
        #endregion

        #region excel
        private void SaveResultToExcel(string excelFilePath,
            Dictionary<string, Dictionary<string, Dictionary<string, FieldStat>>> schemaTblColFieldStats)
        {
            DataSet ds=new DataSet("DB profile");
            foreach(string schemaName in schemaTblColFieldStats.Keys)
            {
                foreach(string tblName in schemaTblColFieldStats[schemaName].Keys)
                {
                    DataTable dt=new DataTable(schemaName +"."+tblName);
                    dt.Columns.Add("FieldName", typeof (string));
                    dt.Columns.Add("DataType", typeof (SimpleDataType));
                    dt.Columns.Add("Count", typeof (int));
                    dt.Columns.Add("DistinctCount", typeof (int));
                    dt.Columns.Add("NullCount", typeof(int));
                    dt.Columns.Add("Max", typeof (object));
                    dt.Columns.Add("Min", typeof(object));
                    dt.Columns.Add("Mean", typeof (object));
                    dt.Columns.Add("Mode", typeof(object));
                    dt.Columns.Add("SD", typeof(object));
                    dt.Columns.Add("Distributions", typeof (string));

                    foreach(string colName in schemaTblColFieldStats[schemaName][tblName].Keys)
                    {
                        FieldStat fieldStat = schemaTblColFieldStats[schemaName][tblName][colName];
                        DataRow dr = dt.NewRow();
                        dr["FieldName"] = fieldStat.FieldName;
                        dr["DataType"] = fieldStat.DataType;
                        dr["Count"] = fieldStat.Count;
                        dr["DistinctCount"] = fieldStat.DistinctValueCount;
                        dr["NullCount"] = fieldStat.MissingValueCount;
                        dr["Max"] = fieldStat.MaxValue;
                        dr["Min"] = fieldStat.MinValue;
                        dr["Mean"] = fieldStat.Average;
                        dr["Mode"] = fieldStat.Mode;
                        dr["SD"] = fieldStat.SD;
                        string distribution = string.Empty;
                        if(fieldStat.Distributions !=null && fieldStat.Distributions.Count>0)
                        {
                            foreach(object key in fieldStat.Distributions.Keys)
                            {
                                int freq = fieldStat.Distributions[key];
                                if (distribution.Length > 0)
                                    distribution += ",";
                                distribution += string.Format("{0}:{1}", key.ToString(), freq);
                            }
                        }
                        dr["Distributions"] = distribution;

                        dt.Rows.Add(dr);
                    }

                    ds.Tables.Add(dt);
                }
            }

            ExcelWriter.CreateWorkbook(excelFilePath, ds);
        }
        #endregion
    }
}
